package cmp.compile;
// GO has been disabled for now
/*********************************************************
 * Loads the data from all PAVE databases to be analyzed
 * Currently also loads peptide file from AAfiles directory
 * 
 * unitrans table has unique library names, where each has its respective normalized count
 * unitrans table also has a list of exp counts and a list of normalized counts
 * 			we may end up removing the normalized count list
 * 			the exp count list is used in Summary
 * groups table has <assm>-<lib> for each assembly-library combination, which contains the summed counts
 */
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.TreeMap;
import java.util.TreeSet;
import java.util.Vector;
import java.util.HashMap;
import java.util.HashSet;

import javax.swing.JOptionPane;

import util.Debug.ErrorReport;
import util.methods.Converters;
import cmp.database.*;
import assem.DBConn;
import cmp.main.Globals;
import cmp.compile.panels.CompilePanel;
import cmp.compile.LoadAAfiles;

public class LoadPAVE {
	private static final long PRINT_INTERVAL = 1000;
	private static final long MAX_RESULTS_PER_QUERY = 100000;
	private static final long MAX_RANK = 3; // load top N from PAVE to cmpPAVE

	public LoadPAVE(DBConn dbc, CompilePanel panel) {
		cmpDBC = dbc;
		cmpPanel = panel;
		
    		new Version(dbc, "v1.1");
    		
    		if (!step1_assemblyTable()) return;
    		if (!Schema.addDynamicColumns(dbc, panel)) return;
     	if (!step3_uniqueHitsTable()) return;  	
		if (!step4_unitransTable()) return;	    
		if (!step5_unitransHitsTable()) return;
		// if (!step7_goTables()) return;

		new LoadAAfiles(dbc, panel, utMap);
		new DBinfo(dbc, true);		// loads columns into info table
	}
		
	/*********************************************
	 * creates the table of databases used in this cmpPAVE
	 */
	private boolean step1_assemblyTable() {
   		long startTime = CompileMain.getTime();
		CompileMain.PrtDateMsg("\nCreating DataSet Table");
	
		try {  			
   			for(int x=0; x<cmpPanel.getAssemblyCount(); x++) {
			
				CompileMain.PrtSpMsg(1, "Adding " + cmpPanel.getAssemblyName(x) + "::" 
				                                    + cmpPanel.getAssemblyDB(x));
				String remark = cmpPanel.getAssemblyRemark(x);
				
   				DBConn paveDBC = CompileMain.getDBConnection(cmpPanel, x);
				
				ResultSet rs = paveDBC.executeQuery(
						"SELECT assemblyid, username, assemblydate, annotationdate " +
						"FROM assembly");
				if (!rs.next()) {
					String msg = "Incomplete or invalid sTCW database: " +
							cmpPanel.getAssemblyName(x) + ":"  + cmpPanel.getAssemblyDB(x)
							+ "\nMake sure you Instantiate";
					System.out.println(msg);
					JOptionPane.showMessageDialog(null, msg,
							"Invalid database", JOptionPane.PLAIN_MESSAGE);
					return false;
				}
				String sqlAsm =  
						"ASMstr=" +   		quote(rs.getString("assemblyid")) 		+ "," +	
						"prefix=" +			quote(cmpPanel.getAssemblyPrefix(x))	+ "," +
						"username=" + 		quote(rs.getString("username")) 		+ "," +
						"assemblydate=" + 	quote(rs.getString("assemblydate")) 	+ "," +
						"annotationdate=" + quote(rs.getString("annotationdate")) 	+ "," +
						"remark=" +			quote(remark) + ",";			
				
				rs = paveDBC.executeQuery("SELECT dbDate FROM pja_databases");
				if (rs.next()) // maybe not annotated
					sqlAsm += "annoDBdate=" + quote(rs.getString("dbDate")) + ",";
				
				rs = paveDBC.executeQuery("SELECT COUNT(*) FROM pja_databases");
	    			if (rs.next()) 
	    				sqlAsm += "nAnnoDB=" + rs.getInt(1);
	    			else 
	    				sqlAsm += "nAnnoDB=0";
	    			
	    			if (DBinfo.tableHasColumn(paveDBC, "assem_msg", "peptide")) {
	    				sqlAsm += ", isPep=1";
	    			}
				
				cmpDBC.executeUpdate("INSERT INTO assembly SET " + sqlAsm);
				
				String field = "A__" + cmpPanel.getAssemblyName(x);
			    try {
			    		cmpDBC.executeUpdate("ALTER TABLE pog_groups ADD " + field + " integer default 0");
			    }
			    catch (Exception e) {
			    		CompileMain.PrtError("Your selected sTCW databases have duplicate singleTCW IDs: " 
			    				+ cmpPanel.getAssemblyName(x));
			    		CompileMain.PrtSpMsg(2, "The only way to change an ID is to rebuild one of the databases");
			    		System.exit(-1);
			    }
			}
			//cmpDBC.commitChanges();
			CompileMain.PrtSpMsgTime(1, "Total datasets " + cmpPanel.getAssemblyCount(), startTime);
			return true;
		} catch(Exception e) {
			ErrorReport.die(e, "Cannot open database - make sure it exists");
			return false;
		}
	}

	/****************************************************
	 * Build table of  unique hits 
	 * Everything is loaded so have HITid when sequence hits are loaded,
	 * but as only hits with rank<= 3 are added, some extra hits get loaded
	 */
	private boolean step3_uniqueHitsTable() {
		CompileMain.PrtDateMsg("\nCreating Unique hit table");
  		long startTime = CompileMain.getTime();

		try {
   			int HITid=1;
   			
   			for(int x=0; x<cmpPanel.getAssemblyCount(); x++) {
   	  			CompileMain.PrtSpMsg(2, "DataSet " + cmpPanel.getAssemblyName(x));
   				DBConn paveDBC = CompileMain.getDBConnection(cmpPanel, x);
   				
   				boolean haveGODB = false; // CAS CompileMain.checkGODB();
   				if (haveGODB) CompileMain.checkAddGoColumns();
    	  					
				int limit=0, numRows=0, count=0, prtCnt=0;
				ResultSet rs;
				
				numRows = paveDBC.executeCount("SELECT COUNT(*) FROM pja_db_unique_hits");       		
				do {        		
					limit += MAX_RESULTS_PER_QUERY;	        		
					rs = paveDBC.executeQuery(
							"SELECT hitID, dbtype, taxonomy, isProtein, description, " +
									"species, length, sequence FROM pja_db_unique_hits ORDER BY hitID ASC " +
									" LIMIT " + (limit-MAX_RESULTS_PER_QUERY) + ", " + MAX_RESULTS_PER_QUERY );
	  	  
					while(rs.next()) 
					{
						count++; prtCnt++; 
						
	    					if(prtCnt % PRINT_INTERVAL == 0) 
	        					System.out.print("         Writing Unique Hits table " + 
	        					((int)((((double) count)/numRows)*100)) + "%                \r");
	    					String HITstr = rs.getString(1); //rs.getString("hitID"); 
						
	    					if (!hitMap.containsKey(HITstr)) 
	    					{				
		    					cmpDBC.executeUpdate(	
									"INSERT INTO unique_hits " +
										"(HITid, HITstr, dbtype, taxonomy, isProtein, " +
										"description, species, length, sequence) VALUES (" +
										HITid 						+ "," +
										quote(HITstr) 				+ "," +
										quote(rs.getString(2)) 		+ "," + // "dbtype"
										quote(rs.getString(3)) 		+ "," +	// "taxonomy"
										rs.getInt(4) 				+ "," + // "isProtein"
										quote(rs.getString(5)) 		+ "," +	// "description"
										quote(rs.getString(6)) 		+ "," + // "species"
										rs.getInt(7) 				+ "," +	// "length"
										quote(rs.getString(8)) 		+ // "sequence"
										")");
		    					hitMap.put(HITstr, HITid);
		    					HITid++;
	    					}
    					}
    					rs.close();
        			} while(limit<=numRows); // end do 
        		
				if (haveGODB) {
					CompileMain.PrtSpMsg(2, "Collect GO terms...              ");
					String godb = CompileMain.strGODB;
					String query = "update unique_hits as h, " + godb + ".PAVE_Uniprot as up " + 
								" set h.go_list=up.go where up.UPid=h.HITstr";
					cmpDBC.executeUpdate(query);
					ResultSet rs2 = cmpDBC.executeQuery("select count(*) from unique_hits where go_list != ''");
					rs2.first();
					int nWithGO = rs2.getInt(1);
					rs2 = cmpDBC.executeQuery("select count(*) from unique_hits");
					rs2.first();
					int nTotal = rs2.getInt(1);
					CompileMain.PrtSpMsg(2, nWithGO + " have GO terms (out of " + nTotal + ")");
				}
				paveDBC.close();
        			System.out.print("                                                   \r");
				CompileMain.PrtSpMsg(3, "Unique hits " + count);
			} // end loop through databases
   			
   			CompileMain.PrtSpMsgTimeMem(1, "Unique hits across assemblies " + hitMap.size(), 
    					startTime);
   			return true;
		} catch(Exception e) {
			ErrorReport.reportError(e);
			return false;
		}
	}
	
	/****************************************************
	 * create table of all unitrans (this includes ones not in the peptide file)
	 */
	private boolean step4_unitransTable() {
		CompileMain.PrtDateMsg("\nCreating Sequence table");	
  		long startTime = CompileMain.getTime();
		int UTid = 0, ASMid=0;
    	
		String sqlSR1 = "SELECT pja_db_unitrans_hits.uniprot_id, pja_db_unitrans_hits.e_value," +
				"contig.contigid, contig.consensus, contig.numclones, contig.consensus_bases, " +
				"contig.totalexp, contig.totalexpN ";
		String sqlSR3 = " FROM contig " +
				"LEFT JOIN pja_db_unitrans_hits ON pja_db_unitrans_hits.PID = contig.PIDov " +
				"ORDER by contig.contigid ASC ";	
		try {    		
     		for(int x=0; x<cmpPanel.getAssemblyCount(); x++) {
     			boolean isProteinDB = cmpPanel.isProteinDB(x);
     			
       			CompileMain.PrtSpMsg(2, "DataSet " + cmpPanel.getAssemblyName(x));	
				DBConn paveDBC = CompileMain.getDBConnection(cmpPanel, x);
	
				Vector<String> uniqueLibs = new Vector<String> ();   			
	   	  		long thisTime = CompileMain.getTime();
	   	  		int limit = 0, numRows = 0;
	   	  		int nUT=0, nLib=0, nAnnoUT=0, prtCnt=0;
	   	  		long nAlign=0, nExp=0;
       			ASMid++;
	   		
       		// Create dynamic part of query
       			// get libraries for this source assembly
	   			String sqlSR2="";
	   			Vector <String> libList = new Vector <String>();
	   			
	   	   		ResultSet rs = paveDBC.executeQuery("select libid from library where ctglib=0"); 
				while (rs.next())
				{
    					String lib = rs.getString(1);
    					sqlSR2 += ", " + Globals.PRE_S_CNT + lib + ", " + Globals.PRE_S_RPKM + lib;
    					libList.add(lib);
				}
				rs.close();
				nLib = libList.size();
			
				// get DE columns for this dataset
				Vector <String> deList = new Vector <String> ();
				rs = paveDBC.executeQuery("show columns from contig"); 
				while (rs.next())
				{
    					String de = rs.getString(1);
    					if (de.startsWith(Globals.PRE_S_DE)) {
    						sqlSR2 += ", " + de;		// for singleTCW query
    						de = de.substring(Globals.PRE_S_DE.length()); // for multiTCW insert
    						deList.add(de);
    					}
				}
				rs.close();				
        		  		
	  		// read all contigs for this assembly (read 'limit' contigs at a time)
	   			numRows = paveDBC.executeCount("SELECT COUNT(*) FROM contig");
	   			do {        		
	   				limit += MAX_RESULTS_PER_QUERY;
	   				String sqlSR4 = " LIMIT " + (limit-MAX_RESULTS_PER_QUERY) + ", " 
        											+ MAX_RESULTS_PER_QUERY; 
	   				rs = paveDBC.executeQuery(sqlSR1 + sqlSR2 + sqlSR3 + sqlSR4);

	   				// create sql insert for multiTCW database
	   				while(rs.next()) {
	        				nUT++; prtCnt++; 
	    					
	    					if(prtCnt == PRINT_INTERVAL) {
	    						prtCnt=0;
	        					System.out.print("         Writing Sequence table " + 
	        							((int)((((double) nUT)/numRows)*100)) + "%       " + nExp + "         \r");
	        				}
	    					nAlign += rs.getLong("numclones");
	        				nExp   += rs.getLong("totalexp");
	        				
	        		 		String UTstr = rs.getString("contigid");
	        				UTid++;
	        				utMap.put(UTstr, UTid);  
	        					 	
	        			// start building SQL string from source database
	        			// length gets overwritten by protein length
	        				String sqlTG =  "INSERT INTO unitrans SET " + 
        						"UTid = " + UTid  								+ "," +
        						"UTstr= " + quote(UTstr) 						+ "," + 
        						"ASMid= " + ASMid 								+ "," +
        						"ntSeq= " + quote(rs.getString("consensus"))		+ "," + 
        						"numAlign= " + rs.getInt("numclones") 			+ "," + 
        						"ntLen= " + rs.getInt("consensus_bases")			+ "," + 
        						"totExp= " + rs.getInt("totalexp")				+ "," + 
        						"totExpN= " + rs.getInt("totalexpN")				 		
        						;		        				
	   
	        				if (isProteinDB)
	        					sqlTG += "," + 
	        					"aaSeq= " + quote(rs.getString("consensus")) +  "," +
	        					"aaLen= " + rs.getInt("consensus_bases");
	        							
		        			// add HITid, HITstr - this is best annotation (PIDov)
	        		 		String HITstr = rs.getString("uniprot_id");
	        		 		int HITid=0; // PAVE has the default HITid=null; cmpPAVE uses HITid=0
	        		 		if (HITstr != null) {
	        		 			nAnnoUT++;
	        		 			if (hitMap.containsKey(HITstr)) 
	        					{
	        						HITid = hitMap.get(HITstr);
	        		 				sqlTG += "," + 
	        		 						"HITid= " + HITid + "," +  
	        		 						"HITstr= " + quote(HITstr) + "," + 
	        		 						"e_value=" + rs.getDouble("e_value");
	        		 			}
	        		 			else CompileMain.die("No unique hit for dataset ID " + ASMid + " hit " + HITstr);
	        		 		}
	        		 		else sqlTG += ", HITid=0, HITstr=NULL, e_value=NULL ";

		        		 	// add expList, expListN plus the actual LN values
	        		 		String expList="", expListN="";
	        		 		for (int i=0; i<libList.size(); i++) {
	        		 			String lib = libList.get(i);
	        		 			int eA = rs.getInt(Globals.PRE_S_CNT + lib);
	        		 			double eN = rs.getDouble(Globals.PRE_S_RPKM + lib);
	        		 	
	        		 			if(!expMap.containsKey(cmpPanel.getAssemblyName(x))) {
	        		 				expMap.put(cmpPanel.getAssemblyName(x), lib);
	        		 				uniqueLibs.add(lib);
	        		 			}
	        		 			else {
	        		 				if(!uniqueLibs.contains(lib)) {
	        		 					uniqueLibs.add(lib);
	        		 					expMap.put(cmpPanel.getAssemblyName(x), expMap.get(cmpPanel.getAssemblyName(x)) + " " + lib);
	        		 				}
	        		 			}
	        		 			String eNs = Globals.saveDouble(eN);
	        		 			expList +=  String.format("%s=%d ", lib, eA);
	        		 			expListN += String.format("%s=%s ", lib, eNs);
	        		 			sqlTG += ", " + Globals.PRE_LIB + lib + "=" + eNs;
	        		 		}
	        		 		sqlTG += "," + "expList=" + quote(expList) + "," + "expListN=" + quote(expListN);	  	
	        		 		
	        		 		// add the DE values
	        		 		for (String de : deList) {
	        		 			double p = rs.getDouble(Globals.PRE_S_DE + de);
	        		 			sqlTG += ", " + Globals.PRE_DE + de +  "=" + Globals.saveDouble(p); 
	        		 		}
		        			cmpDBC.executeUpdate(sqlTG);
		        		} // end while through this set of rows
        			
	   				rs.close();
		        } while(limit<=numRows);
		        		
	        		paveDBC.close(); 
	        		System.out.print("                                                   \r");
	        		CompileMain.PrtSpMsgTimeMem(3, "Sequences " + nUT, thisTime);
	        		
	        		// add information to the assembly table
	        		cmpDBC.executeUpdate("update assembly set " +
        				"nUT = " 		+ nUT 		+ "," +
        				"nAnnoUT = " 	+ nAnnoUT 	+ "," +
        				"nAlign = " 		+ nAlign 	+ "," +
        				"nExp = " 		+ nExp		+ ", " +
        				"nLib = "		+ nLib		+ " " +
        				"where ASMid = " + ASMid);
     		} // end loop through databases
     		
     		CompileMain.PrtSpMsgTime(1, "Total sequence " + utMap.size(), startTime);
     		return true;
		}
		catch (Exception e) {
			ErrorReport.prtReport(e, "Creating sequence table");
			return false;
		} 
	}
	
	/****************************************************
	 * Build table of  unitrans hits 
	 * All sequence hits are added that have a rank <= 3, which includes the best anno and best eval
	 */
	private boolean step5_unitransHitsTable() {
		CompileMain.PrtDateMsg("\nCreating Sequence hit table");
  		long startTime = CompileMain.getTime();

		try {
   			for(int x=0; x<cmpPanel.getAssemblyCount(); x++) {
   	  			CompileMain.PrtSpMsg(2, "DataSet " + cmpPanel.getAssemblyName(x));
   				DBConn paveDBC = CompileMain.getDBConnection(cmpPanel, x);
   				
   				boolean haveGODB = CompileMain.checkGODB();
   				if (haveGODB) CompileMain.checkAddGoColumns();
    	  					
				int limit=0, numRows=0, count=0, prtCnt=0;
				ResultSet rs;
				
				numRows = paveDBC.executeCount("SELECT COUNT(*) FROM pja_db_unitrans_hits");    
		
				do {        		
					limit += MAX_RESULTS_PER_QUERY;	        		
					rs = paveDBC.executeQuery(
						"SELECT contigid, uniprot_id, percent_id, alignment_len, " +
							"ctg_start, ctg_end, prot_start, prot_end, e_value, bit_score, dbtype, taxonomy, " +
							"filter_best, filter_ovbest " +
							"FROM pja_db_unitrans_hits where blast_rank <=" + MAX_RANK + 
							" LIMIT " + (limit-MAX_RESULTS_PER_QUERY) + ", " + MAX_RESULTS_PER_QUERY );
	  	  
					while(rs.next()) 
					{
						String CTGstr = rs.getString(1);  
    						if (!utMap.containsKey(CTGstr)) ErrorReport.die("can't find " + CTGstr);
    						int CTGid = utMap.get(CTGstr);
    						String HITstr = rs.getString(2);
						if (!hitMap.containsKey(HITstr)) ErrorReport.die("can't find " + HITstr);
						int HITid = hitMap.get(HITstr);
						
						count++; prtCnt++; 
						
	    					if(prtCnt == PRINT_INTERVAL) {
	    						prtCnt=0;
	        					System.out.print("         Writing Sequence Hits table " + 
	        					((int)((((double) count)/numRows)*100)) + "%                \r");
	    					}
	    					String tp = rs.getString(11);
	    					String tx = rs.getString(12);
	    					if (tp.length() > 2) tp = tp.substring(0,2);
	    					if (tx.length() > 3) tx = tx.substring(0,3);
	    					String type = 	tp.toUpperCase() + tx;
	    					
	    					cmpDBC.executeUpdate(	
								"INSERT INTO unitrans_hits " +
									"(HITid, HITstr, UTid, UTstr, percent_id," +
									"alignment_len, seq_start, seq_end, hit_start, hit_end, e_value, bit_score, " +
									"type, bestEval, bestAnno) " +
									"VALUES (" +
									HITid 						+ "," +
									quote(HITstr) 				+ "," +
									CTGid 						+ "," +
									quote(CTGstr) 				+ "," +
									rs.getInt(3) 				+ "," + // percent_id
									rs.getInt(4)			 		+ "," +	// alignment len
									rs.getInt(5) 				+ "," + // seq_start
									rs.getInt(6)			 		+ "," +	// seq_end
									rs.getInt(7)			 		+ "," + // hit_start
									rs.getInt(8) 				+ "," +	// hit_end
									rs.getDouble(9)				+ "," + // e_value
									rs.getFloat(10)				+ "," +	// bit score
									quote(type) 					+ "," + 
									rs.getInt(13)				+ "," + // filter_best
									rs.getInt(14)				+		// filter_bestov	
									")");
    					}
    					rs.close();
        			} while(limit<=numRows); // end do 
        		
				paveDBC.close();
        			System.out.print("                                                   \r");
				CompileMain.PrtSpMsg(3, "Sequence hits " + count);
			} // end loop through databases
   			
   			CompileMain.PrtSpMsgTimeMem(1, "Unique hits across sTCW databases " + hitMap.size(), 
    					startTime);
   			return true;
		} catch(Exception e) {
			ErrorReport.reportError(e);
			return false;
		}
	}

	// Import the unitrans/go, uniprot/go, go_info, and gotree tables for the combined GO Set
	// of the projects. 
	private boolean step6_goTables()
	{
		if (!projectsHaveGO()) return true;
		try
		{
			DBConn db = cmpDBC;
			ResultSet rs = null;
			// First the pja_uniprot_go table which maps uniprot to its GOs, including all ancestors
			if (db.hasTable("uniprot_go"))
			{
				db.executeUpdate("drop table uniprot_go");
			}		
			System.err.println("Make uniprot_go table");
			db.executeUpdate("create table uniprot_go (HITid int, gonum int, unique(HITid,gonum),index(gonum))");
			
			System.err.println("Fill go_info");
			if (db.hasTable("go_info")) db.executeUpdate("drop table go_info");
			db.executeUpdate("create table go_info (gonum int, descr tinytext, " +
					" term_type enum('biological_process','cellular_component','molecular_function'), unique(gonum))");
	
			System.err.println("Fill unitrans_go");
			if (db.hasTable("unitrans_go")) db.executeUpdate("drop table unitrans_go");
			db.executeUpdate("create table unitrans_go (UTid bigint, gonum int, bestHit tinytext, bestScore double, unique(UTid,gonum), index(gonum))");
			
			// First we have to get the UTid and HITid mappings
			TreeMap<String,Integer> utName2ID = new TreeMap<String,Integer>();
			TreeMap<String,Integer> hitName2ID = new TreeMap<String,Integer>();
			rs = db.executeQuery("select UTstr, UTid from unitrans");
			while (rs.next())
			{
				utName2ID.put(rs.getString(1), rs.getInt(2));
			}
			rs = db.executeQuery("select HITstr, HITid from unique_hits");
			while (rs.next())
			{
				hitName2ID.put(rs.getString(1), rs.getInt(2));
			}
			
			// Now just go through the corresponding tables in the projects and load them into cmppave,
			// ignoring duplicates if any. 
			TreeSet<Integer> goSeen = new TreeSet<Integer>();
			TreeSet<Integer> upSeen = new TreeSet<Integer>();
			PreparedStatement ps1 = db.prepareStatement("insert into uniprot_go (HITid,gonum) values(?,?)");
			PreparedStatement ps2 = db.prepareStatement("insert into unitrans_go (UTid,gonum,bestHit,bestScore) values(?,?,?,?)");
			PreparedStatement ps3 = db.prepareStatement("insert into go_info (gonum,descr,term_type) values(?,?,?)");
	 		for(int x=0; x<cmpPanel.getAssemblyCount(); x++) 
	 		{
	 			String aName = cmpPanel.getAssemblyName(x);
	   			CompileMain.PrtSpMsg(2, "Load GOs from  " + cmpPanel.getAssemblyName(x));	
				DBConn db2 = CompileMain.getDBConnection(cmpPanel, x);
	
				int added = 0;
				rs = db2.executeQuery("select hitID, gonum from pja_uniprot_go as g join pja_db_unique_hits as h "  +
						" on h.DUHID=g.DUHID");
				while (rs.next())
				{
					String hitName = rs.getString(1);
					if (!hitName2ID.containsKey(hitName))
					{
						//CompileMain.PrtWarn("Uniprot " + hitName + " found in " + aName + " but not in cmpPAVE");
						continue;
					}
					int hitID = hitName2ID.get(hitName);
					if (upSeen.contains(hitID)) continue;
					int gonum = rs.getInt(2);
					ps1.setInt(1, hitID);
					ps1.setInt(2, gonum);
					ps1.execute();
					upSeen.add(hitID);
					added++;
				}
				CompileMain.PrtSpMsg(1, "Added " + added + " uniprot_go entries");
				
				added=0;
				rs = db2.executeQuery("select contigid, gonum, hitID, bestScore from pja_unitrans_go as g join pja_db_unique_hits as h "  +
				" on h.DUHID=g.bestDUH join contig as c on c.CTGID=g.CTGID");
				while (rs.next())
				{
					String utName = rs.getString(1);
					if (!utName2ID.containsKey(utName))
					{
						CompileMain.PrtWarn("Sequence " + utName + " found in " + aName + " but not in multiTCW");
						continue;
					}
					int utID = utName2ID.get(utName);
					int gonum = rs.getInt(2);
					ps2.setInt(1, utID);
					ps2.setInt(2, gonum);
					ps2.setString(3,rs.getString(3));
					ps2.setDouble(4, rs.getDouble(4));
					ps2.execute();
					added++;
				}
				CompileMain.PrtSpMsg(1, "Added " + added + " unitrans_go entries");
				
				added=0;
				rs = db2.executeQuery("select gonum, descr, term_type from go_info");
				while (rs.next())
				{
					int gonum = rs.getInt(1);
					if (goSeen.contains(gonum)) continue;
					goSeen.add(gonum);
					ps3.setInt(1, gonum);
					ps3.setString(2,rs.getString(2));
					ps3.setString(3,rs.getString(3));
					ps3.execute();
					added++;
				}
				CompileMain.PrtSpMsg(1, "Added " + added + " go_info entries");
	 		}
	 		//
	 		// GO tree: for this we need access to the GO database, to ensure consistency
	 		//
			if (db.hasTable("go_tree")) db.executeUpdate("drop table go_tree");			
	    		db.executeUpdate("create table go_tree (tblidx int unsigned primary key auto_increment, " +
	    			" gonum int unsigned, level smallint unsigned, name tinytext," + 
	    			" term_type enum('biological_process','cellular_component','molecular_function')," +
	    			" index(gonum) )");
	 		if (CompileMain.checkGODB())
	 		{
	 			String godb = CompileMain.strGODB;
	 			db.executeUpdate("insert into go_tree select 0,gt.gonum, gt.level, g.descr, g.term_type  " +
		    			" from " + godb + ".PAVE_gotree as gt join go_info as g on g.gonum=gt.gonum " +
		    			" order by gt.idx asc");
	 		}
	 		else
	 		{
	 			CompileMain.PrtWarn("Can't find GO database; unable to create GO tree view");
	 		}
	 		
	 		// POG GO table
			if (db.hasTable("pog_go")) db.executeUpdate("drop table pog_go");			
	 		db.executeUpdate("create table pog_go (PGid bigint unsigned, gonum int, bestUTid bigint unsigned, " +
	 				" bestHit tinytext, bestScore double, unique(PGid,gonum), index(gonum))");
	 		return true;
		}
		catch(Exception e)
		{
			ErrorReport.prtReport(e, "Creating GO tables");
			return false;
		}
	}

	// currently not called from anywhere
	boolean projectsHaveGO()
	{
		boolean ret = true;
		try
		{
	 		for(int x=0; x<cmpPanel.getAssemblyCount(); x++) 
	 		{
				DBConn db2 = CompileMain.getDBConnection(cmpPanel, x);
				if (!db2.hasTable("pja_unitrans_go"))
				{
					System.err.println("Project " + cmpPanel.getAssemblyName(x) + " does not have GO annotation.");
					System.err.println("GO annotation will not be added to mTCW");
					ret = false;
					break;
				}
	 		}
		}
		catch(Exception e)
		{
			CompileMain.PrtWarn("Unable to determine if sTCW projects have GO annotation.");
			ret = false;
		}
		return ret;
	}
	private String quote(String word) {
		if (word==null || word.equalsIgnoreCase("null"))
		{
			return word;
		}
		return "\"" + word + "\""; 
	}
	CompilePanel cmpPanel;
	DBConn cmpDBC;
	
	private HashMap <String, Integer> utMap = new HashMap <String, Integer> ();  // contig ID, UTid
	private HashMap <String, Integer> hitMap = new HashMap <String, Integer> (); // UniProt ID, HITid
	private HashMap <String, String>  expMap = new HashMap <String, String> ();
}
